import pandas as pd
import pymysql
from flask import Flask, jsonify, request
from moduels import dishes_moduel
from sqlalchemy import create_engine, and_,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer
from sqlalchemy.orm import sessionmaker
from moduels.dishes_moduel import Dishes, UFSDishes, ProductsByShape, DishDetail, BasicMatches, TFCMatchDish,Products,MTCustomers
from moduels import dishes_moduel
import json
from utils import BaiDuOCR,BaiDuPOI
from utils.BaiDuPOI import getPOI

app = Flask(__name__)


@app.route('/')
def hello_world():
    return 'hello,world'


@app.route('/dishes/<string:dish_class>')
def hello_world2(dish_class):
    dish_ret_dicts = []
    print(dish_class)
    session = sessionmaker(bind=dishes_moduel.engine)()
    dish_rets = session.query(Dishes).filter(Dishes.three_class_name == dish_class).all()
    for dish_ret in dish_rets:
        dish_ret_dict = dish_ret.to_json()
        dish_ret_dicts.append(dish_ret_dict)
    session.close()

    return jsonify(dish_ret_dicts)


# 热卖菜单
@app.route('/hotdish')
def hotdish():
    dish_ret_dicts = []
    session = sessionmaker(bind=dishes_moduel.engine)()
    dish_rets = session.query(UFSDishes).filter(UFSDishes.ishot == 1).all()
    print(dish_rets)
    for dish_ret in dish_rets:
        dish_ret_dict = dish_ret.to_json()
        dish_ret_dicts.append(dish_ret_dict)
    session.close()
    print(jsonify(dish_ret_dicts))

    return jsonify(dish_ret_dicts)
# 新品菜单
@app.route('/newdish')
def newdish():
    dish_ret_dicts = []
    session = sessionmaker(bind=dishes_moduel.engine)()
    dish_rets = session.query(UFSDishes).filter(UFSDishes.ishot == 0).all()
    print(dish_rets)
    for dish_ret in dish_rets:
        dish_ret_dict = dish_ret.to_json()
        dish_ret_dicts.append(dish_ret_dict)
    session.close()
    print(jsonify(dish_ret_dicts))

    return jsonify(dish_ret_dicts)

# swiper轮播图
@app.route('/swiper')
def swiper():
    dish_ret_dicts = []
    session = sessionmaker(bind=dishes_moduel.engine)()
    dish_rets = session.query(UFSDishes).filter(UFSDishes.swiper == 1).all()
    for dish_ret in dish_rets:
        dish_ret_dict = dish_ret.to_json()
        dish_ret_dicts.append(dish_ret_dict)
    session.close()
    return jsonify(dish_ret_dicts)


# 推荐菜品
@app.route('/introduce')
def introduce():
    dish_ret_dicts = []
    session = sessionmaker(bind=dishes_moduel.engine)()
    dish_rets = session.query(UFSDishes).filter(UFSDishes.introduce == 1).all()
    print(dish_rets)
    for dish_ret in dish_rets:
        dish_ret_dict = dish_ret.to_json()
        dish_ret_dicts.append(dish_ret_dict)
    session.close()
    return jsonify(dish_ret_dicts)


@app.route('/products_by_shape')
def products_by_shape():
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456Wy', database='juhui',
                           charset='utf8')
    cur = conn.cursor()
    sql = "SELECT shape,concat('[',GROUP_CONCAT('{',char(34),'product_name',char(34),':',char(34),product_name,char(34),',',char(34), 'product_code',char(34),':',char(34),product_code,char(34),',',char(34), 'pic_url',char(34),':',char(34),pic_url,char(34),'}'),']') AS infors FROM products_by_shape GROUP BY shape;"
    cur.execute(sql)
    result = cur.fetchall()
    datas = []
    for i in range(len(result)):
        a = result[i][1]
        b = json.loads(a)
        data = {"shape": result[i][0], "infor": b}
        datas.append(data)
    final_result = datas

    print(final_result)
    print(type(final_result))
    json_data = json.dumps(final_result)

    return json_data


@app.route('/dishdetail_by_id/<int:dish_id>')
def dishdetail_by_id(dish_id):
    dish_ret_dicts = []

    session = sessionmaker(bind=dishes_moduel.engine)()
    dish_rets = session.query(DishDetail).filter(DishDetail.dish_id == dish_id).all()
    for dish_ret in dish_rets:
        dish_ret_dict = dish_ret.to_json()
        dish_ret_dicts.append(dish_ret_dict)
    session.close()

    return dish_ret_dicts


# 根据搜索的关键字，味型匹配基础应用适合的模块
@app.route('/searchdata', methods=["POST"])
def searchData():
    # 定义一个空数组用于装数据并转为json返回回去
    result_ret_dicts = []
    data = request.get_data()
    print(data)
    # 获取前端返回的搜索关键字。

    searchdata_json = request.get_data().decode('utf-8')
    searchdata_dic = json.loads(searchdata_json)
    if searchdata_dic['searchData']['taste'] != []:
        smell_taste = searchdata_dic['searchData']['taste'][-1]['text']
        print(smell_taste)

        session = sessionmaker(bind=dishes_moduel.engine)()
        result_rets = session.query(BasicMatches).filter(BasicMatches.smell_taste == smell_taste).all()
        print(result_rets)
        for result_ret in result_rets:
            if result_ret != None:
                result_ret_dict = result_ret.to_json()
                # print(result_ret_dict)
                result_ret_dicts.append(result_ret_dict)
        session.close()
    else:
        result_ret_dicts = ['没有匹配数据']
    print(result_ret_dicts)
    return jsonify(result_ret_dicts)


# 根据TFC展现建议菜品及其详细信息
@app.route('/getResultByTRC', methods=['POST'])
def getResultByTFC():
    # 定义一个空数组用于装数据并转为json返回回去
    result_ret_dicts = {"message": {"total": 0, "pagenum": 1, "data": []}}
    # 创建数据库查询的session对象
    session = sessionmaker(bind=dishes_moduel.engine)()
    # 获取前端返回的搜索关键字。
    searchdata_json = request.get_data().decode('utf-8')
    searchdata_dic = json.loads(searchdata_json)

    print(searchdata_dic)
    # 判断输入的三个值是否是空值
    smell_taste_list = searchdata_dic['searchData']['taste']
    cook_list = searchdata_dic['searchData']['cook']
    food_list = searchdata_dic['searchData']['food']
    pagenum = searchdata_dic['searchData']['pagenum']

    # 对前台传递过来的数据进行筛选请求数据库，一共8种情况
    if smell_taste_list != [] and cook_list != [] and food_list != []:

        smell_taste = searchdata_dic['searchData']['taste'][-1]['text']
        cook = searchdata_dic['searchData']['cook'][0]['text']
        food = searchdata_dic['searchData']['food'][-1]['text']
        print(type(smell_taste))
        print(cook)
        print(food)
        print(type(TFCMatchDish.smell))
        result_rets = session.query(TFCMatchDish).filter(
            and_(TFCMatchDish.smell == smell_taste, TFCMatchDish.cook == cook, TFCMatchDish.food == food)).limit(
            10).all()
        print(result_rets)
    elif smell_taste_list != [] and cook_list != []:
        smell_taste = searchdata_dic['searchData']['taste'][-1]['text']
        cook = searchdata_dic['searchData']['cook'][0]['text']

        result_rets = session.query(TFCMatchDish).filter(
            and_(TFCMatchDish.smell == smell_taste, TFCMatchDish.cook == cook)).limit(10).all()
    elif cook_list != [] and food_list != []:
        cook = searchdata_dic['searchData']['cook'][0]['text']
        food = searchdata_dic['searchData']['food'][-1]['text']

        result_rets = session.query(TFCMatchDish).filter(
            and_(TFCMatchDish.cook == cook, TFCMatchDish.food == food)).limit(10).all()
    elif smell_taste_list != [] and food_list != []:
        smell_taste = searchdata_dic['searchData']['taste'][-1]['text']
        food = searchdata_dic['searchData']['food'][-1]['text']

        result_rets = session.query(TFCMatchDish).filter(
            and_(TFCMatchDish.smell == smell_taste, TFCMatchDish.food == food)).limit(10).all()

    elif smell_taste_list != []:
        smell_taste = searchdata_dic['searchData']['taste'][-1]['text']
        result_rets = session.query(TFCMatchDish).filter(
            and_(TFCMatchDish.smell == smell_taste)).limit(10).all()
    elif cook_list != []:
        # 此处代码用于测试分页功能
        # cook = searchdata_dic['searchData']['cook'][0]['text']
        # result_rets = session.query(TFCMatchDish).filter(
        #     and_(TFCMatchDish.cook == cook)).limit(10).offset((pagenum-1)*10)
        cook = searchdata_dic['searchData']['cook'][0]['text']
        result_rets = session.query(TFCMatchDish).filter(
            and_(TFCMatchDish.cook == cook)).limit(10).all()
    elif food_list != []:
        food = searchdata_dic['searchData']['food'][-1]['text']
        result_rets = session.query(TFCMatchDish).filter(
            and_(TFCMatchDish.food == food)).limit(10).all()

    for result_ret in result_rets:
        result_ret_dict = result_ret.to_json()
        # print(result_ret_dict)
        result_ret_dicts['message']['data'].append(result_ret_dict)
    total = len(result_ret_dicts['message']['data'])
    result_ret_dicts['message']['total'] = total
    session.close()

    return result_ret_dicts


# 录入客户信息，并初步预估适合该客户的产品和菜品应用
@app.route('/customer', methods=['POST'])
def intruduce_by_customer():
    poiss=[]
    shopper_names=[]
    # 定义一个空数组用于装数据并转为json返回回去
    result_ret_dicts = {"message": {"total": 0, "pagenum": 1,'competition':[{'competition_num':0},{'competition_price':[]},{'competition_rate_times':[]},{'competition_chain':[]}], "data": [], "args": []}}
    # 创建数据库查询的session对象
    session = sessionmaker(bind=dishes_moduel.engine)()
    # 通过get_json方法直接获取前端传递的dict字典数据
    data = request.get_json()
    print(data)
    # 从前端获取的data拿到价值信息
    shop_name = data['formdata']['shop_name']
    # 获取地址信息和经纬度
    shop_place = data['formdata']['shop_place']
    latitude = data['latitude']
    longitude = data['longitude']
    catering = data['formdata']['catering']
    smell_taste = data['formdata']['smell']
    cook = data['formdata']['cooking']
    food = data['formdata']['food']

    # 根据定位信息获取周边
    # api_key = "uFhXnbEV82tFALLNVnTTJp4yZtKwddzY"
    # location=str(latitude)+','+str(longitude)
    # radius=3000
    # keyword='餐饮'
    # # 获取10页内容
    # for i in range(10):
    #     pois=getPOI(api_key, location, radius, keyword,page_num=i)
    #     print(pois)
    #     for poi in pois:
    #         poiss.append(poi)
    # print(poiss)
    # print(len(poiss))
    # for poi in poiss:
    #     shopper_names.append(poi.get('name'))
    # print(shopper_names)
    # print(len(shopper_names))
    # # 根据周边门店名称获取美团信息,,是否在数组元素内用in_
    # #     筛选周边门店在美团中有信息的
    # result_shopper = session.query(MTCustomers).filter(MTCustomers.shop_name.in_(shopper_names)).all()
    # result_ret_dicts['message']['competition'][0]['competition_num']=len(result_shopper)
    # #       美团有信息+均价在20-50之间的
    # result_ave_price20_50=session.query(MTCustomers).filter(MTCustomers.shop_name.in_(shopper_names),MTCustomers.ave_price.between(20,50)).all()
    # result_ave_price50_100 = session.query(MTCustomers).filter(MTCustomers.shop_name.in_(shopper_names),MTCustomers.ave_price.between(51, 100)).all()
    # result_ave_price100_200 = session.query(MTCustomers).filter(MTCustomers.shop_name.in_(shopper_names),MTCustomers.ave_price.between(101, 200)).all()
    # result_ave_price200_500 = session.query(MTCustomers).filter(MTCustomers.shop_name.in_(shopper_names),MTCustomers.ave_price.between(201, 500)).all()
    # result_ret_dicts['message']['competition'][1]['competition_price'].append(len(result_ave_price20_50))
    # result_ret_dicts['message']['competition'][1]['competition_price'].append(len(result_ave_price50_100))
    # result_ret_dicts['message']['competition'][1]['competition_price'].append(len(result_ave_price100_200))
    # result_ret_dicts['message']['competition'][1]['competition_price'].append(len(result_ave_price200_500))
    # print(len(result_ave_price20_50))
    # print('-----------------')
    # print(result_shopper)
    # df=pd.DataFrame([(r.shop_name, r.chain,r.rate,r.rate_times,r.ave_price,r.catering_class) for r in result_shopper], columns=['shop_name', 'chain','rate','rate_times','ave_price','catering_class'])
    # print(df)
    # df_chain_sum=pd.pivot_table(df,index=['chain'],values=['ave_price'],aggfunc='count')
    # print(df_chain_sum)

    # 数据库查看适合菜品信息
    result_rets = session.query(TFCMatchDish).filter(
        and_(TFCMatchDish.smell == smell_taste, TFCMatchDish.cook == cook, TFCMatchDish.food == food)).limit(
        10).all()
    print('***************')
    print(result_rets)

    for result_ret in result_rets:
        result_ret_dict = result_ret.to_json()
        # print(result_ret_dict)
        result_ret_dicts['message']['data'].append(result_ret_dict)
    result_ret_dicts['message']['args'].append(shop_name)
    result_ret_dicts['message']['args'].append(shop_place)
    result_ret_dicts['message']['args'].append(catering)
    total = len(result_ret_dicts['message']['data'])
    result_ret_dicts['message']['total'] = total
    session.close()
    print(result_ret_dicts)
    return result_ret_dicts
    # print(df_chain_sum.to_json())
    # return df_chain_sum.to_json()


@app.route('/uploaddishmenu', methods=['POST'])
def uploaddishmenu():
    result_ret_dicts = {"message": {"total": 0, "pagenum": 1, "data": []}}
    # 获取前端传递的图片流，但是里面还有其他信息
    data = request.get_data()
    if data:
        # 取出其中的图片
        temp_data = data.split(b'\r\n\r\n')[1]
        data_img = temp_data.split(b'\r\n--')[0]
        # 将图片存在本地
        with open('./temp/temp.jpg', 'wb') as f:
            f.write(data_img)
            print('上传图片已存本地')
        f.close()
        # 调用百度接口识别图片并返回内容存在响应体的字段中
        res_data = BaiDuOCR.getData('./temp/temp.jpg')
        result_ret_dicts['message']['data'].append(res_data)
    return result_ret_dicts


@app.route('/exportresult')
def exportResult():
    print('客户端请求ppt方案')
    #     读取本地ppt文件
    temp_model_path='/static/jhmodel.pdf'
    #     返回前段
    return temp_model_path
# 获取产品名称列表
@app.route('/products')
def products():
    print('调用产品')
    product_ret_dicts = []
    session = sessionmaker(bind=dishes_moduel.engine)()
    result_rets = session.query(Products).filter(Products.location == '1').all()
    for result_ret in result_rets:
        product_ret_dict = result_ret.to_json()
        product_ret_dicts.append(product_ret_dict)
    session.close()
    return jsonify(product_ret_dicts)
# 获取单个产品详细信息
@app.route('/productDetail',methods=['POST'])
def productDetail():
    name=request.get_json()['key']
    print(name)
    product_ret_dicts = []
    session = sessionmaker(bind=dishes_moduel.engine)()
    result_rets = session.query(Products).filter(Products.name == name).first()
    product_detail_dict=result_rets.to_json()
    print(product_detail_dict)
    # for result_ret in result_rets:
    #     product_ret_dict = result_ret.to_json()
    #     product_ret_dicts.append(product_ret_dict)
    # session.close()
    return jsonify(product_detail_dict)

if __name__ == '__main__':
    app.run(port=5000, host='0.0.0.0',debug=True)
